// PositionRepository.java
package com.hibernate.hrm.dao;

import com.hibernate.hrm.entity.Department;
import com.hibernate.hrm.entity.Position;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
public interface PositionRepository extends JpaRepository<Position, Integer> {
    List<Position> findByDepartmentID_DepartmentID(Integer departmentId);
    @Query("SELECT p FROM Position p WHERE " +
            "(:departmentId IS NULL OR p.departmentID.departmentID = :departmentId) AND " +
            "(:positionName IS NULL OR p.positionName LIKE %:positionName%) AND " +
            "(:positionCode IS NULL OR p.positionCode LIKE %:positionCode%)")
    Page<Position> findByFilters(
            @Param("departmentId") Integer departmentId,
            @Param("positionName") String positionName,
            @Param("positionCode") String positionCode,
            Pageable pageable);

    @Query("SELECT p FROM Position p WHERE p.departmentID IN :departments")
    Page<Position> findByDepartments(
            @Param("departments") List<Department> departments,
            Pageable pageable);

    boolean existsByPositionCode(String positionCode);

    List<Position> findByDepartmentID(Department department);

    @Query("SELECT COUNT(u) FROM User u WHERE u.jobPositionID = :position AND u.status = true")
    long countActiveEmployeesByPosition(@Param("position") Position position);
}